<?php
class SQL {
	protected static $_dbh = null; //静态属性,所有数据库实例共用,避免重复连接数据库
	protected $_dbType = 'mysql';
	protected $_pconnect = false; //是否使用长连接
	protected $_host = 'localhost';
	protected $_port = 3306;
	protected $_user = 'root';
	protected $_pass = 'root';
	protected $_dbName = null; //数据库名
	protected $_sql = false; //最后一条sql语句
	protected $_cte = '';
	protected $_where = '';
	protected $_order = '';
	protected $_limit = '';
	protected $_field = '*';
	protected $_clear = 0; //状态，0表示查询条件干净，1表示查询条件污染
	protected $_trans = 0; //事务指令数
	protected $_left = '`'; //字段或表名前缀
	protected $_right = '`'; //字段或表名后缀

	public $status = null;
	
	/**
	 * 初始化类
	 * @param array $conf 数据库配置
	 */
	public function __construct($conf) {
		$this->_host   = $conf['host'];
		$this->_port   = $conf['port'];
		$this->_user   = $conf['user'];
		$this->_pass   = $conf['pass'];
		$this->_dbName = $conf['dbname'];
		if (isset($conf['type']))
			$this->_dbType = strtolower($conf['type']);
		if ($this->_dbType == 'mssql') {
			$this->_left  = '[';
			$this->_right = ']';
		} else {
			$this->_left  = '`';
			$this->_right = '`';
		}
		//连接数据库
		if (is_null(self::$_dbh))
			$this->status = $this->_connect();
	}
	/**
	 * 连接数据库的方法
	 */
	protected function _connect() {
		if (!class_exists('PDO'))
			return "未开启PDO扩展！";	

		if ($this->_dbType == 'mssql') {
			$dsn = 'sqlsrv:server=' . $this->_host . ';database=' . $this->_dbName;
			try {
				$dbh = new PDO($dsn, $this->_user, $this->_pass);
				$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //设置如果sql语句执行错误则抛出异常，事务会自动回滚
			} catch (PDOException $e) {
				return '连接数据库失败：[' . $e->getCode() . ']' . $e->getMessage();
			}
		} else {
			$dsn     = $this->_dbType . ':host=' . $this->_host . ';port=' . $this->_port . ';dbname=' . $this->_dbName;
			$options = $this->_pconnect ? array(
				PDO::ATTR_PERSISTENT => true,
				PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true
			) : array();
			try {
				$dbh = new PDO($dsn, $this->_user, $this->_pass, $options); //报错参考 https://blog.csdn.net/aerchi/article/details/80780112
				$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //设置如果sql语句执行错误则抛出异常，事务会自动回滚
				$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); //禁用prepared statements的仿真效果(防SQL注入)
			} catch (PDOException $e) {
				switch ($e->getCode()) {
					case 2002:
						return '连接数据库失败，数据库地址填写错误！';
						break;
					case '1045':
						return '连接数据库失败，数据库用户名或密码填写错误！';
						break;
					case '1049':
						return '连接数据库失败，数据表不存在！';
						break;
					default:
						return '连接数据库失败，[' . $e->getCode() . ']' . $e->getMessage();
				}
			}
			$dbh->exec('SET NAMES utf8');
		}
		self::$_dbh = $dbh;
		return true;
	}

	/**
	 * 字段和表名添加 `符号
	 * 保证指令中使用关键字不出错 针对mysql
	 * @param string $value
	 * @return string
	 */
	protected function _addChar($value) {
		$value = trim($value);
		if ($value != '*' && !preg_match('/(^\(|\)$)/', $value)) {
			if (!preg_match('/^' . preg_quote($this->_left, '/') . '/', $value) && !preg_match('/' . preg_quote($this->_right, '/') . '$/', $value)) {
				$value = $this->_left . $value . $this->_right;
			}
		}
		return $value;
	}

	/**
	 * 取得数据表的字段信息
	 * @param string $tbName 表名
	 * @return array
	 */
	protected function _tbFields($tbName) {
		$sql  = $this->_dbType == 'mssql' ? "SELECT * FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = '{$tbName}'" : 'SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME="' . $tbName . '" AND TABLE_SCHEMA="' . $this->_dbName . '"';
		$stmt = self::$_dbh->prepare($sql);
		$stmt->execute();
		$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
		$ret    = array();
		foreach ($result as $key => $value) {
			$ret[$value['COLUMN_NAME']] = $value;
		}
		return $ret;
	}

	/**
	 * 过滤并格式化数据表字段
	 * @param string $tbName 数据表名
	 * @param array $data POST提交数据
	 * @return array $newdata
	 */
	protected function _dataFormat($tbName, $data) {
		if (!is_array($data))
			return array();
		$table_column = $this->_tbFields($tbName);
		$ret          = array();
		foreach ($data as $key => $val) {
			if (array_key_exists($key, $table_column)) {
				$default = $table_column[$key]['COLUMN_DEFAULT'];
				$type    = $table_column[$key]['DATA_TYPE'];
				$isNull  = $table_column[$key]['IS_NULLABLE'];
				if ($val === null || $val === '')
					$ret[$this->_addChar($key)] = $isNull == 'NO' ? $default == null ? 'NULL' : ($default == '' ? "''" : $default) : 'NULL';
				else if (!is_scalar($val))
					continue; //值不是标量则跳过
				else {
					if (is_bool($val))
						$val = intval($val);
					else if (is_numeric($val))
						$val = intval($val);
					else if (is_float($val))
						$val = floatval($val);
					else if (preg_match('/^\s*\(\s*\w*(\+|\-|\*|\/)?\w*\s*\)$/i', $val) || preg_match('/^\s*\w+\s*\(\s*\)\s*$/i', $val)) {
						// 支持在字段的值里面直接使用其它字段 ,例如 (score+1) (name) 必须包含括号
						$val = $val;
					} else if (is_string($val)) {
						$val = "'" . str_replace("'", "''", $val) . "'";
					}
					$ret[$this->_addChar($key)] = $val;
				}
			}
		}
		return $ret;
	}

	/**
	 * 执行查询 主要针对 SELECT, SHOW 等指令
	 * @param string $sql sql指令
	 * @return mixed
	 */
	protected function _doQuery($sql = '', $all = true) {
		$this->_sql = $sql;
		$pdostmt    = self::$_dbh->prepare($this->_sql); //prepare或者query 返回一个PDOStatement
		$pdostmt->execute();
		$result = $all ? $pdostmt->fetchAll(PDO::FETCH_ASSOC) : $pdostmt->fetch(PDO::FETCH_ASSOC);
		return $result;
	}

	/**
	 * 执行语句 针对 INSERT, UPDATE 以及DELETE,exec结果返回受影响的行数
	 * @param string $sql sql指令
	 * @return integer
	 */
	protected function _doExec($sql = '') {
		$this->_sql = $sql;
		return self::$_dbh->exec($this->_sql);
	}

	/**
	 * 执行sql语句，自动判断进行查询或者执行操作
	 * @param string $sql SQL指令
	 * @return mixed
	 */
	public function doSql($sql = '', $all = true) {
		$queryIps = '(INSERT|UPDATE|DELETE|REPLACE|CREATE|DROP|LOAD DATA|SELECT .* INTO|COPY|ALTER|GRANT|REVOKE|LOCK|UNLOCK)';
		if (preg_match('/^\s*"?(' . $queryIps . ')\s+/i', $sql, $m)) {
			return $this->_doExec($sql);
		} else {
			//查询操作
			return $this->_doQuery($sql, $all);
		}
	}

	/**
	 * 获取最近一次查询的sql语句
	 * @return String 执行的SQL
	 */
	public function getLastSql() {
		return $this->_sql;
	}

	/**
	 * 插入方法
	 * @param string $tbName 操作的数据表名
	 * @param array $data 字段-值的一维数组
	 * @return int 受影响的行数
	 */
	public function insert($tbName, $data = array()) {
		$data = $this->_dataFormat($tbName, $data);
		if (!is_array($data) || empty($data))
			return;
		$sql = 'INSERT INTO ' . $this->_addChar($tbName) . " (" . implode(', ', array_keys($data)) . ") VALUES (" . implode(", ", array_values($data)) . ")";
		
		return $this->_doExec($sql);
	}

	/**
	 * 删除方法
	 * @param string $tbName 操作的数据表名
	 * @return int 受影响的行数
	 */
	public function delete($tbName) {
		//安全考虑,阻止全表删除
		if (!trim($this->_where))
			return false;
		$sql          = 'DELETE FROM ' . $this->_addChar($tbName) . ' ' . $this->_where;
		$this->_clear = 1;
		$this->_clear();
		return $this->_doExec($sql);
	}

	/**
	 * 更新函数
	 * @param string $tbName 操作的数据表名
	 * @param array，string $data 参数数组或字符串
	 * @return int 受影响的行数
	 */
	public function update($tbName, $data) {
		//安全考虑,阻止全表更新
		if (!trim($this->_where) || empty($data))
			return false;
		if (is_array($data)) {
			$data = $this->_dataFormat($tbName, $data);
			
			$valArr = array();
			foreach ($data as $k => $v) {
				$valArr[] = $k . ' = ' . $v;
			}
			$valStr = implode(',', $valArr);
		} else
			$valStr = $data;
		$sql = 'UPDATE ' . $this->_addChar($tbName) . ' SET ' . trim($valStr) . ' ' . trim($this->_where);
		return $this->_doExec($sql);
	}

	/**
	 * 查询函数
	 * @param string $tbName 操作的数据表名
	 * @return array 结果集
	 */
	public function select($tbName = '', $all = true) {
		$sql = $this->_cte . "SELECT {$this->_field} FROM " . $this->_addChar($tbName) . " {$this->_where} {$this->_order} {$this->_limit}";
		if ($this->_dbType == 'mssql' && $this->_limit != '')
			$sql = str_replace(array(
				'{field}',
				'{table}',
				'{where}'
			), array(
				trim($this->_field),
				trim($tbName),
				trim($this->_where) == '' ? 'WHERE 1=1' : trim($this->_where)
			), $this->_limit);
		$this->_clear = 1;
		$this->_clear();
		return $this->_doQuery(trim($sql), $all);
	}
	
	/**
	 * 获取表总行数
	 * @param  string $tbName 操作的数据表名
	 */
	public function count($tbName = '') {
		$sql          = $this->_cte . "SELECT COUNT(*) AS {$this->_addChar('count')} FROM " . $this->_addChar($tbName) . " " . trim($this->_where);
		$this->_clear = 1;
		$this->_clear();
		return ($res = $this->_doQuery(trim($sql), false)) ? $res['count'] : null;
	}
	
	/**
	 * 获取最小缺失id
	 * @param  string $tbName 操作的数据表名
	 * @param  string $id 操作的字段
	 */
	public function missMin($tbName = '', $id = 'id') {
		$sql          = sprintf(trim('
			SELECT COALESCE( Min( a.%2$s + 1 ), 1 ) AS %2$s
			FROM
				%1$s a 
			WHERE
				NOT EXISTS (
					SELECT 1 FROM %1$s AS b WHERE b.%2$s = a.%2$s + 1
				) 
				AND EXISTS (
					SELECT 1 FROM %1$s WHERE %2$s = 1
				)
			'), $this->_addChar($tbName), $this->_addChar($id));
		$this->_clear = 1;
		$this->_clear();
		return ($res = $this->_doQuery(trim($sql), false)) ? $res[$id] : null;
	}
	
	/**
	 * @param mixed $option 组合条件的二维数组，例：$option['field1'] = array(1,'=>','or')
	 * @return $this
	 */
	public function where($option) {
		if ($this->_clear > 0)
			$this->_clear();
		$this->_where = ' WHERE ';
		$logic        = 'AND';
		if ($option == null)
			$this->_where .= '1=1';
		else if (is_string($option)) {
			$this->_where .= trim($option) == '' ? '1=1' : $option;
		} else if (is_array($option)) {
			foreach ($option as $k => $v) {
				if (is_array($v)) {
					$relative = isset($v[1]) ? trim($v[1]) : '=';
					if (strtolower($relative) === 'in') {
						if (is_string($v[0]))
							$condition = $this->_addChar($k) . " IN ('{$v[0]}') ";
						else if (is_array($v[0]))
							$condition = $this->_addChar($k) . " in ('" . implode("', '", $v[0]) . "') ";
					} else {
						$logic     = isset($v[2]) ? $v[2] : 'AND';
						$condition = ' (' . $this->_addChar($k) . ' ' . $relative . ' ' . $v[0] . ') ';
					}
				} else {
					$logic     = 'AND';
					$condition = $v === null ? " ({$this->_addChar($k)} IS NULL) " : " ({$this->_addChar($k)} = '{$v}') ";
				}
				$this->_where .= isset($mark) ? $logic . $condition : $condition;
				$mark = 1;
			}
		}
		$this->_where = trim($this->_where);
		return $this;
	}

	/**
	 * 设置排序
	 * @param mixed $option 排序条件数组 例:array('sort'=>'desc')
	 * @return $this
	 */
	public function order($option) {
		if ($this->_clear > 0)
			$this->_clear();
		$this->_order = ' ORDER BY ';
		if (is_string($option)) {
			$this->_order .= $option;
		} else if (is_array($option)) {
			foreach ($option as $k => $v) {
				$order = $this->_addChar($k) . ' ' . $v;
				$this->_order .= isset($mark) ? ',' . $order : $order;
				$mark = 1;
			}
		}
		$this->_order = trim($this->_order);
		return $this;
	}
	
	/**
	 * 设置查询行数及页数
	 * @param int $page pageSize不为空时为页数，否则为行数
	 * @param int $pageSize 为空则函数设定取出行数，不为空则设定取出行数及页数
	 * @return $this
	 */
	public function limit($page, $pageSize = null) {
		//select top (@limit) * from [a_AdminUser] where id>3 and id not in (select top (@page * @limit) id from [a_AdminUser] where id>3 order by id asc)
		if ($this->_clear > 0)
			$this->_clear();
		if ($this->_dbType == 'mssql') {
			$this->_limit = "SELECT t.* FROM (
				SELECT [RowNo]=ROW_NUMBER() OVER(ORDER BY a.[ID] ASC), {field}
				FROM [{table}]
				{leftjoin}
				{where}
			) WHERE [RowNo] BETWEEN (({$page} - 1) * {$pageSize} + 1) AND ({$page} * {$pageSize})";
			//$this->_limit = "select top {$pageSize} {field} from [{table}] {where} and id not in (select top (({$page} - 1) * {$pageSize}) id from [{table}] {where} order by id asc)";
		} else if ($pageSize === null) {
			$this->_limit = "LIMIT " . $page;
		} else {
			$pageval      = intval(($page - 1) * $pageSize);
			$this->_limit = "LIMIT " . $pageval . "," . $pageSize;
		}
		return $this;
	}

	/**
	 * 设置查询字段
	 * @param mixed $field 字段数组
	 * @return $this
	 */
	public function field($field = '*') {
		if (empty($field))
			$field = '*';
		if ($this->_clear > 0)
			$this->_clear();
		if (is_string($field))
			$this->_field = trim($field);
		else {
			$nField       = array_map(array(
				$this,
				'_addChar'
			), $field);
			$this->_field = trim(implode(',', $nField));
		}
		if (empty($this->_field))
			$this->_field = '*';
		return $this;
	}
	
	public function cte($cte) {
		$this->_cte = trim($cte);
		return $this;
	}
	
	/**
	 * 设置值增值
	 */
	public function increment($tbName, $number = 1) {
		return $this->_doQuery("ALTER TABLE " . $this->_addChar($tbName) . " AUTO_INCREMENT = {$number}");
	}
	
	/**
	 * 获取最后INSERT的值增值ID
	 */
	public function lastInsert() {
		$row = $this->_doQuery("SELECT LAST_INSERT_ID() AS " . $this->_addChar('id'), false);
		return $row['id'];
	}
	
	/**
	 * 获取数据库版本
	 */
	public function version() {
		$row = $this->_doQuery("SELECT VERSION() AS " . $this->_addChar('version'), false);
		return $row['version'];
	}
	
	/**
	 * 清理标记函数
	 */
	protected function _clear() {
		$this->_cte   = '';
		$this->_where = '';
		$this->_order = '';
		$this->_limit = '';
		$this->_field = '*';
		$this->_clear = 0;
	}

	/**
	 * 手动清理标记
	 * @return $this
	 */
	public function clearKey() {
		$this->_clear();
		return $this;
	}

	/**
	 * 启动事务
	 * @return void
	 */
	public function startTrans() {
		//数据rollback 支持
		if ($this->_trans == 0)
			self::$_dbh->beginTransaction();
		$this->_trans++;
		return;
	}

	/**
	 * 用于非自动提交状态下面的查询提交
	 * @return boolen
	 */
	public function commit() {
		$result = true;
		if ($this->_trans > 0) {
			$result       = self::$_dbh->commit();
			$this->_trans = 0;
		}
		return $result;
	}

	/**
	 * 事务回滚
	 * @return boolen
	 */
	public function rollback() {
		$result = true;
		if ($this->_trans > 0) {
			$result       = self::$_dbh->rollback();
			$this->_trans = 0;
		}
		return $result;
	}

	/**
	 * 关闭连接
	 * PHP 在脚本结束时会自动关闭连接。
	 */
	public function close() {
		if (!is_null(self::$_dbh))
			self::$_dbh = null;
	}
}